<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 15-5-20
 * Time: 下午2:02
 * 异常报告控制器
 */
namespace Admin\Controller;
use Mcit\Page;
class AbnormalController extends BaseController {

    public function index(){

        $month = I("month");
        if(!empty($_GET['oldtime'])){
            $begin     = date("Y-m-d",time()-30*86400);//30天前日期
            $nextmonth = date("Y-m-d",time());//今天日期
            $map['currentTime'] = array("between",array($begin,$nextmonth));
        }
        if(!empty($_GET['month'])){
            $begin     = date("Y-m-01",strtotime($month));//某月的第一天
            $end       = date('Y-m-d', strtotime("$begin +1 month -1 day"));//当前月的最后一天
            $map['currentTime'] = array("between",array($begin,$end));
        }
        //分页
        $count = M("abnormal_total")->where($map)->count();
        vendor('Mcit.Sys.Page');
        $Page = new Page($count, 20);
        $pageView = $Page->show();
        $this->assign('pageView', $pageView); //发送分页
        $nowPage = isset($_GET['page']) ? $_GET['page'] : 1;
        $this->abnormal = M("abnormal_total")->where($map)->order("id desc")->page($nowPage . ',' . $Page->listRows)->select();
        //分页结束
//        $this->china = D("Region")->getChinaArea();
        $this->display();
    }


    //异常电桩列表
    public function abnormal(){

        $key_link = I('key_link');
        $f        = I('location_f_1');
        $s        = I('location_s_1');
        $t        = I('location_t_1');
        $current  = I('current');
        if(!empty($key_link)){
            $sql = "select  *
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    WHERE a.currentTime = '$current'
                    and a.total < 8000
                    and a.key_link like '$key_link%'";
        }else if(!empty($_GET['location_f_1'])){
            if($s == 110100 || $s == 120100 || $s == 310100 || $s == 500100){
                if($s == $t-1){
                    $location = $f.",".$s;
                }else{
                    $location = $f.",".$s.",".$t;
                }
            }else if($s == $t-1){
                $location = $f.",".$s;
            }else{
                $location = $f.",".$s.",".$t;
            }
            $sql = "select  *
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    WHERE a.currentTime = '$current'
                    and a.total < 8000
                    and d.location like '$location%'";
        }else{
            $sql = "select  *
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    WHERE a.currentTime = '$current'
                    and a.total < 8000";
        }
        //分页
        $count = count(M()->query($sql));
//        echo M()->getLastSql();
        vendor('Mcit.Sys.Page');
        $Page = new Page($count, 20);
        $pageView = $Page->show();
        $this->assign('pageView', $pageView); //发送分页
        if($_GET['page'] == 0){
            $nowPage = isset($_GET['page']) ? $_GET['page'] : 0;
        }else{
            $nowPage = isset($_GET['page']) ? $_GET['page'] * 20 - 20 : 0;
        }
        if(!empty($key_link)){
            $sql1 = "select  a.currentTime,a.id,d.name,d.location,b.evc_number,a.key_link,e.fullname
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    left join ev_region e on e.code = substring_index(d.location,',',-1)
                    WHERE a.currentTime = '$current'
                    and a.total < 8000
                    and a.key_link like '$key_link%'
                    limit $nowPage,".$Page->listRows;
        }else if(!empty($_GET['location_f_1'])){
            if($s == 110100 || $s == 120100 || $s == 310100 || $s == 500100){
                if($s == $t-1){
                    $location = $f.",".$s;
                }else{
                    $location = $f.",".$s.",".$t;
                }
            }else if($s == $t-1){
                $location = $f.",".$s;
            }else{
                $location = $f.",".$s.",".$t;
            }
            $sql1 = "select  a.currentTime,a.id,d.name,d.location,b.evc_number,a.key_link,e.fullname
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    left join ev_region e on e.code = substring_index(d.location,',',-1)
                    WHERE a.currentTime = '$current'
                    and a.total < 8000
                    and d.location like '$location%'
                    limit $nowPage,".$Page->listRows;
        }else{
            $sql1 = "select  a.currentTime,a.id,d.name,d.location,b.evc_number,a.key_link,e.fullname
                    from ev_heart_total a
                    LEFT JOIN ev_char b on b.key_link = a.key_link
                    LEFT JOIN char_state_now c on c.key_link = b.key_link
                    left join ev_plant d on d.id = b.ev_plant_id
                    left join ev_region e on e.code = substring_index(d.location,',',-1)
                    WHERE a.currentTime = '$current'
                    and a.total < 8000
                    limit $nowPage,".$Page->listRows;
        }
        $this->current = $current;
        $this->abnors = M()->query($sql1);
//        P($this->abnors);
        //分页结束
        $this->china = D("Region")->getChinaArea();

        $this->display();
    }

    //下线电桩列表
    public function outline(){

        $key_link = I('key_link');
        $f        = I('location_f_1');
        $s        = I('location_s_1');
        $t        = I('location_t_1');
        if(!empty($key_link)){
            $sql = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600
                and a.key_link like '$key_link%'";
        }else if(!empty($_GET['location_f_1'])){
            if($s == 110100 || $s == 120100 || $s == 310100 || $s == 500100){
                if($s == $t-1){
                    $location = $f.",".$s;
                }else{
                    $location = $f.",".$s.",".$t;
                }
            }else if($s == $t-1){
                $location = $f.",".$s;
            }else{
                $location = $f.",".$s.",".$t;
            }
            $sql = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600
                and c.location like '$location%'";
        }else{
            $sql = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600";
        }
        //分页
        $count = count(M()->query($sql));
        vendor('Mcit.Sys.Page');
        $Page = new Page($count, 20);
        $pageView = $Page->show();
        $this->assign('pageView', $pageView); //发送分页
        $nowPage = isset($_GET['page']) ? $_GET['page'] * 20 - 20 : 0;
        if(!empty($key_link)){
            $sql1 = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600
                and a.key_link like '$key_link%'
                limit $nowPage,".$Page->listRows;
        }else if(!empty($_GET['location_f_1'])){
            if($s == 110100 || $s == 120100 || $s == 310100 || $s == 500100){
                if($s == $t-1){
                    $location = $f.",".$s;
                }else{
                    $location = $f.",".$s.",".$t;
                }
            }else if($s == $t-1){
                $location = $f.",".$s;
            }else{
                $location = $f.",".$s.",".$t;
            }
            $sql1 = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600
                and c.location like '$location%'
                limit $nowPage,".$Page->listRows;
        }else{
            $sql1 = "select floor((unix_timestamp(now()) - unix_timestamp(a.time_last)) / 86400) as outline,a.id,a.key_link,b.evc_number,c.`name`,c.location,e.fullname
                from char_online a
                left join ev_char b on b.key_link = a.key_link
                left join ev_plant c on c.id = b.ev_plant_id
                left join char_state_now d on d.key_link = a.key_link
                LEFT JOIN ev_region e on e.`code` = substring_index(c.location,',',-1)
                where a.online = 1
                and (unix_timestamp(now()) - unix_timestamp(a.time_last)) > 600
                limit $nowPage,".$Page->listRows;
        }
        $this->outline = M()->query($sql1);
//        P($this->abnormal);
        //分页结束
        $this->china = D("Region")->getChinaArea();
        $this->display();
    }

    //邮件发送
    public function sendemail(){
        $abnorID        = I("id");
        $this->abnor    = M("abnormal_total")->where("id='".$abnorID."'")->find();
        $s_map['state'] = array('eq',1);
        $company        = M('company')->field('name,id')->where($s_map)->select();
        $data['name']   = '系统管理员';
        $data['id']     = '0';
        $company[]      = $data;
//        $data1['name'] = '会员';
//        $data1['id']   = '00';
//        $company[] = $data1;
        $this->company = $company;

        $r_map['id'] = array('neq','805');
        $role = M('role')->field('name,id')->where($r_map)->select();
        $this->role = $role;

        $map['id'] = array('neq',1);
        $user = M('user')->field('company,email,name,id,department,role')->where($map)->select();
        $this->user = $user;
        if(IS_POST){
//            P($_POST);die;
            $mail  = rtrim($_POST['mail'],',');
            $mails = explode(',',$mail);
            for($i=0;$i<count($mails);$i++)
            {
                $data_mail        = $mails[$i];
                $data_title       = I('title');//主题
                $data_type        = I("type");
                $data_currentTime = I('currentTime');//异常报告时间
                $data_content     = "异常报告数据时间：".I('currentTime').',总电桩数：'.I('total').',在线电桩数：'.I('online').',异常电桩数：'.I('abnormal').',下线电桩数：'.I('outline');
                $data_send_time   = date('Y-m-d H:i:s',time());//发送时间
                $sql_one = "insert into ev_email(title,mail,content,send_time,currentTime,type) values ('$data_title','$data_mail','$data_content','$data_send_time','$data_currentTime','$data_type')";
                $result  = M()->query($sql_one);
                if($result > 0){
                    $sqls = "update ev_abnormal_total set type=2 where id='".I('abnorID')."'";
                    M()->query($sqls);
                    $xls = APP_ROOT_PATH."Public/data/upload/Abnormal_".strtotime($data_currentTime).".xls";
                    if(file_exists($xls)){
                        $xls = APP_ROOT_PATH."Public/data/upload/Abnormal_".strtotime($data_currentTime).".xls";
                        SendMail($mails[$i],$data_title,$data_content,$xls);
                        $this->redirect("Abnormal/index");
                    }else{
                        $this->exportExcel($data_currentTime);
                        $xls = APP_ROOT_PATH."Public/data/upload/Abnormal_".strtotime($data_currentTime).".xls";
                        SendMail($mails[$i],$data_title,$data_content,$xls);
                        $this->redirect("Abnormal/index");
                    }
                }
            }
        }
        $this->display();
    }


    //PHPExcel导出到本地
    private  function exportExcel($current){

        //格式
        $expCellName  = array(
            array('currentTime','记录时间'),
            array('id','ID'),
            array('name','站点名称'),
            array('coordinate','站点坐标（高德）'),
            array('total_ev','所属区域'),
            array('address','站点详细地址'),
            array('evc_number','运营二维码'),
            array('key_link','设备地址'),
            array('fac_name','品牌')
        );
        $sql = "select  a.currentTime,a.id,d.name,d.coordinate,e.fullname,d.address,b.evc_number,a.key_link,f.fac_name
                from ev_heart_total a
                LEFT JOIN ev_char b on b.key_link = a.key_link
                LEFT JOIN char_state_now c on c.key_link = b.key_link
                left join ev_plant d on d.id = b.ev_plant_id
                left join ev_region e on e.code = substring_index(d.location,',',-1)
                left JOIN ev_fac f on f.fac_login_name = b.ev_fac_code
                WHERE a.currentTime = '".$current."'
                and a.total < 8000";
        $expTableData = M()->query($sql);//数据
        $fileName = "Abnormal_".strtotime($current);//文件名
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel ();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

        //填入主标题
        $objPHPExcel->getActiveSheet()->setCellValue('A1', C('COMPANY'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFont()->setBold(true);

        //填入副标题
        $objPHPExcel->getActiveSheet()->setCellValue('A2', '异常电桩列表(导出日期：'.date('Y-m-d',time()).')');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);

        //设置表头行高
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(25);

        //设置居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:K1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');

        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'3', $expCellName[$i][1]);
        }
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+4), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save(trim(APP_ROOT_PATH."Public/data/upload/".$fileName.'.xls'));
    }

    //邮件发送清单
    public function senddetail(){
        $currentTime = I("currentTime");
        //分页
        $count = M("email")->where("type=2 and currentTime='".$currentTime."'")->count();
        vendor('Mcit.Sys.Page');
        $Page = new Page($count, 20);
        $pageView = $Page->show();
        $this->assign('pageView', $pageView); //发送分页
        $nowPage = isset($_GET['page']) ? $_GET['page'] : 1;
        $email   = M("email")->where("type=2 and currentTime='".$currentTime."'")->order("id desc")->page($nowPage . ',' . $Page->listRows)->select();
        //分页结束
        $this->email = $email;
        $this->display();
    }

    //发送清单详情
    public function detail(){
        if(IS_GET){
            $id = I("id");
            $email = M('email')->where("id='".$id."'")->find();
            $email['content'] = strip_tags($email['content']);
            $this->assign('email',$email);
        }
        $this->display();
    }
}